<?php

function konversi_spl(){
	$query = "TRUNCATE TABLE a_spl";
	$result = my_query($query);		
	$query = "TRUNCATE TABLE a_spl_det";
	$result = my_query($query);		
	$query = "TRUNCATE TABLE a_spl_jam";
	$result = my_query($query);		
	
	$query = "SELECT * FROM x_a_spl GROUP BY nomor ORDER BY  spl_id";
	$result = my_query($query );
	while( $ey = my_fetch_array($result) ){
		$waktu = spl_time($ey['nomor']);
		$periode_id=get_periode_id_by_tanggal($ey['tanggal']);
		$jumlah_jam=(int) date("H",strtotime(time_min($waktu['akhir'],$waktu['awal'])));
		$jenis_hari=$ey['jenis_hari'];
		$query = "SELECT * FROM a_spl_tarif WHERE jenis_hari='".$jenis_hari."'";
		$resd = my_query($query);
		$row=array();
		$nno=0;
		while( $ey1 = my_fetch_array($resd) ){
			$row[] = array(
				'jam'=>$ey1['jam'] ,	
				'pengali'=>$ey1['pengali'] ,	
			);	
		}
		//asumsi masing jenis hari hanya punya 2 jenis pengali
		$batas_jam=(int) $row[1]['jam'];
		$batas_jam--;  //batas jam kedua dikurang 1
		$pengali_pertama=$row[0]['pengali'] ;
		$pengali_kedua=$row[1]['pengali'] ;
		$jam_pertama=max(0,$batas_jam);
		$jam_kedua=max(0,$jumlah_jam-$batas_jam);
		if($jumlah_jam<1){
			$hourpay=1;
		}else{	
			$hourpay=($jam_pertama*$pengali_pertama)+($jam_kedua*$pengali_kedua) ;
		}			
		$datas = array(
			'tanggal'=>my_type_data_str($ey['tanggal']),
			'periode_id'=>my_type_data_int($periode_id),
			'nomor'	=>	 my_type_data_str(  $ey['nomor'] ),	
			'karyawan_id'=>my_type_data_int($ey['karyawan_id']),
			'dari_jam'=>my_type_data_str($waktu['awal']),
			'sampai_jam'=>my_type_data_str($waktu['akhir']),
			'tugas'=>my_type_data_str($ey['tugas']),
			'jumlah_jam'=>my_type_data_int($jumlah_jam),
			'deleted'=>my_type_data_str( 'T' ),
		);
		$spl_id= my_insert_record( 'a_spl' , $datas );	
		//update a_spl_det
		$datas = array(
			'jenis'=>my_type_data_str('Tambah'),
			'spl_id'=>my_type_data_int( $spl_id ),
			'tanggal'=>my_type_data_str($ey['tanggal']),
			'periode_id'=>my_type_data_int($periode_id),
			'karyawan_id'=>my_type_data_int($ey['karyawan_id']),
			'dari_jam'=>my_type_data_str($waktu['awal']),
			'sampai_jam'=>my_type_data_str($waktu['akhir']),
			'jumlah_jam'=>my_type_data_int($jumlah_jam),
			'jenis_hari'=>my_type_data_str($jenis_hari),
			'hourpay'=>my_type_data_str($hourpay),
		);
		my_insert_record( 'a_spl_det' , $datas );
		
		$total_fdate = strtotime($ey['tanggal']) + 24 * 60 * 60;
		$tanggal_next = date('Y-m-d', $total_fdate);	
		//update a_spl_jam
		if( strtotime($waktu['akhir'] ) < strtotime( $waktu['awal'] )  ){
			$lintas_hari=true;
			$tmp_sampai='24:00'; 
			$tmp_dari='00:00'; 
		}else{
			$lintas_hari=false;
			$tmp_sampai=$waktu['akhir'] ;
		}
		$datas = array(
			'spl_id'=>my_type_data_int( $spl_id ),
			'tanggal'=>my_type_data_str($ey['tanggal']),
			'dari_jam'=>my_type_data_str($waktu['awal']),
			'sampai_jam'=>my_type_data_str($tmp_sampai),
		);
		my_insert_record( 'a_spl_jam' , $datas );	
		
		if( $lintas_hari ){
			$datas = array(
				'spl_id'=>my_type_data_int( $spl_id ),
				'tanggal'=>my_type_data_str($tanggal_next),
				'dari_jam'=>my_type_data_str($tmp_dari),
				'sampai_jam'=>my_type_data_str($waktu['akhir']),
			);
			my_insert_record( 'a_spl_jam' , $datas );	
		
		}		
	}	
} 		

function spl_time($nomor){
	$query = "SELECT MAX(dari_jam) as awal , MIN(sampai_jam) as akhir FROM x_a_spl WHERE nomor='{$nomor}'";
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row;
}

//================================== konversi_pelamar_seleksi ===============================
function konversi_pelamar_seleksi(){
	$query = "TRUNCATE TABLE pelamar_seleksi";
	$result = my_query($query);	
	
	$query = "SELECT * FROM x_pelamar_seleksi ORDER BY  pelamar_seleksi_id";
	$result = my_query($query );
	while( $ey = my_fetch_array($result) ){
		$query = "SELECT * FROM seleksi_pelamar WHERE nama_seleksi_pelamar='".$ey['kategori_seleksi']."'";
		$resd = my_query($query);	
		$temp = my_fetch_array($resd)  ;
		$datas = array(
			'pelamar_id'=>my_type_data_int($ey['pelamar_id']),
			'tanggal_panggil'=>my_type_data_str($ey['tanggal_panggil']),
			'seleksi_pelamar_id'=>my_type_data_int($temp['seleksi_pelamar_id']),
			'hasil'	=>	 my_type_data_str(  $ey['hasil'] ),	
		);
		my_insert_record( 'pelamar_seleksi' , $datas );		
	}	
} 		


//================================== konversi_pemberhentian ===============================
function konversi_pemberhentian(){
	$query = "TRUNCATE TABLE sk_pemberhentian";
	$result = my_query($query);	
	$query = "TRUNCATE TABLE pemberhentian";
	$result = my_query($query);	
	
	$query = "SELECT * FROM x_pemberhentian ORDER BY  pemberhentian_id";
	$result = my_query($query );
	while( $ey = my_fetch_array($result) ){
 	 		
		$datas = array(
			'nomor'=>my_type_data_str($ey['nomor']),
			'tanggal_surat'=>my_type_data_str($ey['tanggal_surat']),
			'status_henti'=>my_type_data_str($ey['status_henti']),
			'datetime_added'=>my_type_data_str($ey['datetime_added']),
	
		);
		$sk_id = my_insert_record( 'sk_pemberhentian' , $datas );	
	 	 	 	
		$datas = array(
			'sk_id'=>my_type_data_int($sk_id),
			'karyawan_id'=>my_type_data_int($ey['karyawan_id']),
			'tmt'=>my_type_data_str($ey['tmt']),
			'nominal'=>my_type_data_str($ey['nominal']),
			'catatan'=>my_type_data_str($ey['catatan']),
		);
		my_insert_record( 'pemberhentian' , $datas );		
	}	
} 
	
//================================== konversi_absen_payroll ===============================
function konversi_absen_payroll(){
	$query = "SELECT * FROM a_absen_payroll";
	$result = my_query($query );
	while( $ey = my_fetch_array($result) ){
		if( $ey['kelompok_shift_id']==0){
			$karyawans=my_get_data_by_id('kekaryawanan','karyawan_id',$ey['karyawan_id']);
			$query = "UPDATE a_absen_payroll  SET kelompok_shift_id={$karyawans['kelompok_shift_id']} WHERE karyawan_id={$ey['karyawan_id']} AND  periode_id={$ey['periode_id']}";
			my_query($query);	
		}	
	}	
} 

//================================== konversi_realisasi_log ===============================
function konversi_realisasi_log(){
	$query = "TRUNCATE TABLE a_realisasi_log";
	$result = my_query($query);	
	
	$query = "SELECT * FROM x_a_realisasi_log ORDER BY realisasi_log_id";
	$result = my_query($query );
	while( $ey = my_fetch_array($result) ){
		$periodes=my_get_data_by_id('pr_periode','periode_id',$ey['periode_id']);
		$tgl= (int) substr($ey['field_tgl'],4,2) ;
		if( $tgl<11){
			$ftanggal=$periodes['sampai'] ;
		}else{	
			$ftanggal=$periodes['dari'] ;
		}
		list(   $year1 ,$month1 ,$day1 ) = explode( "-" , $ftanggal );
		$tanggal = $year1.'-'.$month1.'-'.substr($ey['field_tgl'],4,2) ;
		$get_reals=get_realisasi($tanggal , $ey['karyawan_id'], $ey['periode_id'], $ey['sc_jadwal_kerja_kelompok_id']);
		$pc=get_pc($ey['status_absen_id'], $ey['jam_datang'] , $ey['flag_masuk'], $ey['jam_pulang'], $ey['flag_pulang'] );
		$datas = array(
				'realisasi_id'=>my_type_data_int($ey['realisasi_id']),
				'karyawan_id'=>my_type_data_int($ey['karyawan_id']),
				'field_tgl' =>my_type_data_str($ey['field_tgl']),
				'jam_datang' =>my_type_data_str($ey['jam_datang']),	
				'jam_pulang'=>my_type_data_str($ey['jam_pulang']),
				'sc_jadwal_kerja_kelompok_id'=>my_type_data_int($get_reals['sc_jadwal_kerja_kelompok_id']),
				'flag_masuk'=>my_type_data_str($ey['flag_masuk']),
				'flag_pulang'=>my_type_data_str($ey['flag_pulang']),
				'gl_status'=>my_type_data_str($get_reals['gl_status']),
				'gl_time'=>my_type_data_str($get_reals['gl_time']),
				'sc_time'=>my_type_data_str($get_reals['sc_time']),
				'sc_status'=>my_type_data_str($get_reals['sc_status']),
				'status_absen_id'=>my_type_data_int($ey['status_absen_id']), 	
				'pc' =>my_type_data_str($pc),	
				'periode_id'=>my_type_data_int($ey['periode_id']),
		);		
		my_insert_record( 'a_realisasi_log' , $datas );	
	}	
} 


function get_realisasi($tanggal , $karyawan_id, $periode_id, $sc_jadwal_kerja_kelompok_id  ){
	$query = "SELECT a.kelompok_shift_id 
		FROM a_absen_payroll a 
		INNER JOIN kekaryawanan b ON a.karyawan_id= b.karyawan_id 
		WHERE a.karyawan_id={$karyawan_id} AND  a.periode_id={$periode_id}";
	$result = my_query($query );
	$a_absen_payrolls = my_fetch_array($result);
	if( isset( $a_absen_payrolls['kelompok_shift_id'] ) ){
		$kelompok_shift_id=$a_absen_payrolls['kelompok_shift_id'] ;
	}else{
		$karyawans=my_get_data_by_id('kekaryawanan','karyawan_id',$karyawan_id);
		if( isset( $karyawans['kelompok_shift_id'] ) ){
			$kelompok_shift_id=$karyawans['kelompok_shift_id'] ;
		}else{
			$kelompok_shift_id=1;
		}
	}		
	$kelompok_shifts=my_get_data_by_id('a_kelompok_shift','kelompok_shift_id',$kelompok_shift_id) ;
	$waktu_kerja_id=isset( $kelompok_shifts['waktu_kerja_id']) ? $kelompok_shifts['waktu_kerja_id'] : 0; 
	$kelompok=isset( $kelompok_shifts['kelompok_shift_kode']) ? $kelompok_shifts['kelompok_shift_kode'] : ''; 
	
	$datas = array();
// global	
	if($waktu_kerja_id>3 ){
		$kelompok=get_kelompok_shift_jadwal($tanggal, $kelompok_shift_id );
		$datas['gl_status'] =$kelompok;
	}else{
		if(get_hari_libur($tanggal)){	
			$datas['gl_status']= 'X';
		}else{
			if($waktu_kerja_id==1 ){
				$datas['gl_status']= 'N';
			}else{
				$datas['gl_status']= 'F';
			}	
		}
	}
	if($datas['gl_status']=="X"){
		$datas['gl_time'] = '';
	}else{
		$hari_id=get_hari_id_by_date($tanggal);
		$query = "SELECT jam_masuk,jam_pulang FROM a_jadwal_kerja 
				WHERE waktu_kerja_id = {$waktu_kerja_id} 
				AND kelompok='".$kelompok."' 
				AND hari_id={$hari_id}";
		$result = my_query($query);
		$row = my_fetch_array($result);	
		
		if( $row ){
			if($row['jam_masuk']=='00:00:00' AND $row['jam_pulang']=='00:00:00'){			
				$datas['gl_status'] = 'X';
			}
			$t  = date('H:i',strtotime($row['jam_masuk'])).' - '.date('H:i',strtotime($row['jam_pulang'])); 
		}else{
			$t ='';
			$datas['gl_status'] = ' ' ;
		}	
		$datas['gl_time'] = $t ;
	}	
// schedule
	if($sc_jadwal_kerja_kelompok_id==0){
		if($datas['gl_status']=="X"){
			$sc_jadwal_kerja_kelompok_id=21;		
		}else{	
			if($waktu_kerja_id>3){ //kelompoknya A,B.. harus dikonversi ke 1,2,3
				$kelompok=get_kalendar_shift_by_tanggal($tanggal, $kelompok_shift_id );		
			}		
			$query = "SELECT * FROM a_jadwal_kerja_kelompok 
				WHERE waktu_kerja_id = {$waktu_kerja_id}
				AND kelompok='{$kelompok}' ";
			$res = my_query($query);
			$row = my_fetch_array($res);
			$sc_jadwal_kerja_kelompok_id=(int) $row['jadwal_kerja_kelompok_id'];		
		}
	}
	$datas['sc_jadwal_kerja_kelompok_id']=$sc_jadwal_kerja_kelompok_id ;

	$datas['sc_time']=''; //init
	if($sc_jadwal_kerja_kelompok_id>0 AND $sc_jadwal_kerja_kelompok_id<21){
		$jadwal_kerja_kelompoks = my_get_data_by_id('a_jadwal_kerja_kelompok','jadwal_kerja_kelompok_id',$sc_jadwal_kerja_kelompok_id);
		$hari_id=get_hari_id_by_date($tanggal);
		$query = "SELECT jam_masuk,jam_pulang FROM a_jadwal_kerja 
				WHERE waktu_kerja_id =".$jadwal_kerja_kelompoks['waktu_kerja_id']."  
				AND kelompok='".$jadwal_kerja_kelompoks['kelompok']."' 
				AND hari_id={$hari_id}";
		$jams = my_fetch_array(my_query($query));	
			$sc_jam_datang=$jams['jam_masuk'] ;
			$sc_jam_datang=(trim($sc_jam_datang)<>'') ? date('H:i',strtotime($sc_jam_datang)) :'';
			$sc_jam_pulang=$jams['jam_pulang'] ;
			$sc_jam_pulang=(trim($sc_jam_pulang)<>'') ? date('H:i',strtotime($sc_jam_pulang)) :'';
			$datas['sc_time']=$sc_jam_datang.'-'.$sc_jam_pulang ;				
	}
	$statuss=get_status_absen($karyawan_id , $tanggal );
	
	$s='' ;
	foreach($statuss as $status){
		$s.=$status.', ' ;
	}
	$s=substr($s,0,strlen($s)-2) ;	
	$datas['sc_status']=$s ;
	return $datas;
}

function get_pc($status_absen_id, $jam_datang , $flag_masuk, $jam_pulang, $flag_pulang ){
	$telat='' ;
	$statuss=my_get_data_by_id('a_status_absen','status_absen_id',$status_absen_id) ;
	if(	$statuss['hari_kerja']=="Y"){
		if( $jam_datang=='00:00:00' AND $flag_masuk<>'Y'  ){
			$status_absen_id=16 ;
		}elseif(  $jam_pulang=='00:00:00' AND $flag_pulang<>'Y'  ){
			$status_absen_id=16 ;
		}else{	
			$jam_sc1=(trim($sc_jam_datang)=='' ? 0 : strtotime($sc_jam_datang.':00'));
			$jam_sc2=(trim($sc_jam_pulang)=='' ? 0 : strtotime($sc_jam_pulang.':00'));
			$jam_1=(trim($jam_datang)=='' ? 0 : strtotime($jam_datang.':00'));
			$jam_2=(trim($jam_pulang)=='' ? 0 : strtotime($jam_pulang.':00'));
			
			if($jam_sc1 > $jam_sc2){ //lintas tanggal
				
			}else{
				$jam_menit=0;
				if( $jam_1>$jam_sc1 AND $flag_masuk<>'Y'){
						$jam_menit=$jam_1-$jam_sc1;
				}
				if( $jam_sc2>$jam_2 AND $flag_pulang<>'Y'){
						$jam_menit+=($jam_sc2-$jam_2);
				}	
				if($jam_menit>0){
					$telat=gmdate("H:i",$jam_menit );
				}	
			}
		}	

	}
	return $telat;
}

function get_kelompok_shift_jadwal($tanggal , $kelompok_shift_id ){
	$periode_id=get_periode_absen();
	list($yyyy,$mm,$dd) = explode("-" , $tanggal);
	$query = "SELECT tgl_{$dd} FROM a_kalendar_shift 
		WHERE periode_id = {$periode_id} 
		AND kelompok_shift_id={$kelompok_shift_id}";
	$result = my_query($query);
	$re = my_fetch_array($result);
	return $re['tgl_'.$dd];
}


function get_hari_libur($date){
	if(is_sabtu_minggu($date)){
		return true;
	}
	$query="SELECT hari_libur_id FROM a_hari_libur
		WHERE tanggal = '{$date}'";
	if (my_num_rows( my_query($query)) >0){	
		return true;
	}	
	return false;
}

function get_hari_id_by_date($date){
	$hari_id=date("N",strtotime($date) ); //Monday to Sunday
	//konversi ke Sunday to Saturday
	if($hari_id==7){
		$hari_id=1;
	}else{
		$hari_id++;
	}		
	return $hari_id;
}